﻿using System;
using System.Data;
using System.Data.Common;

namespace DataAccess
{
    /// <summary>
    ///SQLHelper 的摘要说明
    /// </summary>
    public class SQLHelper
    {
        private DbConnection connection;

        public SQLHelper()
        {
            this.connection = DbHelper.CreateConnection();
        }

        public SQLHelper(string connectionString)
        {
            this.connection = DbHelper.CreateConnection(connectionString);
        }

        public SQLHelper(string connectionString, string dbProviderName)
        {
            this.connection = DbHelper.CreateConnection(connectionString, dbProviderName);
        }

        public int RunSql(string sql, params DbParameter[] ps)
        {
            int num;
            DbHelper Db = new DbHelper();
            DbCommand cmd = Db.GetSqlStringCommand(sql);

            try
            {
                cmd.Parameters.AddRange(ps);
                cmd.Connection = connection;
                num = Db.ExecuteNonQuery(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cmd.Dispose();
            }

            return num;
        }

        public int RunPrco(string procName, params DbParameter[] ps)
        {
            int num;
            DbHelper Db = new DbHelper();
            DbCommand cmd = Db.GetStoredProcCommand(procName);

            try
            {
                cmd.Parameters.AddRange(ps);
                cmd.Connection = connection;
                num = Db.ExecuteNonQuery(cmd);
            }
            catch (Exception e)
            {

                throw e;
            }
            finally
            {
                cmd.Dispose();
            }
            return num;
        }

        public object GetVar(string sql, params DbParameter[] ps)
        {
            object o;
            DbHelper Db = new DbHelper();
            DbCommand cmd = Db.GetSqlStringCommand(sql);

            try
            {
                cmd.Parameters.AddRange(ps);
                cmd.Connection = connection;
                o = Db.ExecuteScalar(cmd);

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cmd.Dispose();
            }

            return o;
        }

        public DataRow GetRow(string sql, params DbParameter[] ps)
        {
            DataTable dataTable = Select(sql, ps);
            if (dataTable != null)
            {
                if (dataTable.Rows.Count > 0)
                {
                    return Select(sql, ps).Rows[0];
                }
            }

            return null;
        }

        public object Insert(string sql, params DbParameter[] ps)
        {
            return GetVar(sql + ";select SCOPE_IDENTITY();", ps);
        }

        public int Update(string sql, params DbParameter[] ps)
        {
            return RunSql(sql, ps);
        }

        public int Delete(string sql, params DbParameter[] ps)
        {
            return RunSql(sql, ps);
        }

        public DataTable Select(string sql, params DbParameter[] ps)
        {
            DbHelper Db = new DbHelper();
            DbCommand cmd = Db.GetSqlStringCommand(sql);
            DataTable Dt = new DataTable();
            try
            {
                cmd.Parameters.AddRange(ps);
                cmd.Connection = connection;
                Dt = Db.ExecuteDataTable(cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cmd.Dispose();
            }

            return Dt;
        }

        public DbDataReader ExecuteReader(string sql, params DbParameter[] ps)
        {
            DbHelper Db = new DbHelper();
            DbCommand cmd = Db.GetSqlStringCommand(sql);
            try
            {
                cmd.Parameters.AddRange(ps);
                cmd.Connection = connection;
                return Db.ExecuteReader(cmd);
            }
            catch (Exception e)
            {
                connection.Close();
                throw e;
            }
        }
    }
}